Temporary Table

Temporary Table#

MySQL allows us to create temporary tables. Temporary tables are persisted for the duration of the MySQL monitor session and removed once the session is terminated. The user doesn’t need to explicitly clean-up a temporary table. Monitor is the program that displays the MySQL prompt in the terminal.

Syntax#

CREATE TEMPORARY TABLE tableName (

col1 <dataType> <Restrictions>,

col2 <dataType> <Restrictions>,

col3 <dataType> <Restrictions>,

<Primary Key or Index definitions>);

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/5lesson.sh and wait for the MySQL prompt to start-up.

1. Say we want to capture only the first names of actors in a table. We can create a temporary table and populate it with only the first name as follows:

   CREATE TEMPORARY TABLE ActorNames (FirstName CHAR(20));
   

You can observe from the above screenshot that when we log back into the monitor program the temporary table ActorNames has been deleted.

2. Temporary tables can be used to work with intermediate data or results. Also, complex queries with joins or nested queries can be broken up and worked on step-by-step by storing intermediate results in temporary tables.